import pandas as pd
import numpy as np
from prophet import Prophet
from prophet.diagnostics import cross_validation
from prophet.diagnostics import performance_metrics
import plotly.graph_objs as go
import matplotlib.pyplot as plt
import plotly.io as pio
import plotly.express as px
pio.templates.default = "presentation" # o cualquier otro nombre de tema
import re
import sqlalchemy as sa
from sqlalchemy.engine import URL
import urllib
import pyodbc
print('- Paquetes importados exitosamente')
C:\Users\binvelam\Anaconda3\lib\site-packages\scipy\__init__.py:155: UserWarning: A NumPy version >=1.18.5 and <1.25.0 is required for this version of SciPy (detected version 1.26.4
warnings.warn(f"A NumPy version >={np_minversion} and <{np_maxversion}"
- Paquetes importados exitosamente
file = open('config.txt')
lines = file.readlines()
lines = [s.strip() for s in lines]
try:
cnxn = pyodbc.connect('DRIVER={ODBC Driver 11 for SQL Server};Server='
+lines[15]+
';DATABASE='+lines[16]+
';trusted_connection=yes;')
print("conexión exitosa")
except:
print('error al intentar conectar')
conexión exitosa
Bases de Cartera
DF1_BC = pd.read_sql("SELECT Identificacion,Nombre,DiasVencido, FechaProceso, Operacion, YearEmision, SaldoSb204, SaldoDificul, SaldoDificul30, SaldoDificul60, SegmentoFinal FROM BCPersonas WHERE SegmentoFinal IN ('Consumo', 'Vivienda')",cnxn)
print(DF1_BC.shape)
DF1_BC.head(2)
C:\Users\binvelam\Anaconda3\lib\site-packages\pandas\io\sql.py:762: UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy warnings.warn(
(202540, 11)
| Identificacion | Nombre | DiasVencido | FechaProceso | Operacion | YearEmision | SaldoSb204 | SaldoDificul | SaldoDificul30 | SaldoDificul60 | SegmentoFinal | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 0100875905 | Merchan Luco Marina Dolores | 0.0 | 2019-12-30 | 830300962 | 2018 | 9114.21 | 0.0 | 0.0 | 0.0 | Consumo |
| 1 | 1500494453 | Jimenez Ramirez John Manrique | 0.0 | 2020-04-30 | 660302394 | 2018 | 7377.28 | 0.0 | 0.0 | 0.0 | Consumo |
Colaboradores y Ex Colaboradores
DF2_ECBI = pd.read_sql("SELECT DISTINCT epIdentificacion as Identificacion FROM [172.16.66.208].[DATAMART].[dbo].[ExPersonalBanco]",cnxn)
DF2_ECBI['IdColaborador'] = 'Ex Colaborador'
DF2_ECBI[['Identificacion']] = DF2_ECBI[['Identificacion']].astype('str')
print(DF2_ECBI.shape)
DF2_ECBI.head(2)
C:\Users\binvelam\Anaconda3\lib\site-packages\pandas\io\sql.py:762: UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy warnings.warn(
(1705, 2)
| Identificacion | IdColaborador | |
|---|---|---|
| 0 | 0921357141 | Ex Colaborador |
| 1 | 1713935847 | Ex Colaborador |
DF2_CBI = pd.read_sql("SELECT DISTINCT pbIdentificacion as Identificacion FROM [172.16.66.208].[DATAMART].[dbo].[MKPersonalBanco]",cnxn)
DF2_CBI['IdColaborador'] = 'Colaborador'
DF2_CBI[['Identificacion']] = DF2_CBI[['Identificacion']].astype('str')
print(DF2_CBI.shape)
DF2_CBI.head(2)
C:\Users\binvelam\Anaconda3\lib\site-packages\pandas\io\sql.py:762: UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy warnings.warn(
(3575, 2)
| Identificacion | IdColaborador | |
|---|---|---|
| 0 | 0102117306 | Colaborador |
| 1 | 0102132008 | Colaborador |
DF4 = pd.concat([DF2_ECBI, DF2_CBI])
DF3 = pd.read_excel('Id_Tipo_Colaborador.xlsx', dtype={'Identificacion': str})
DF3 = pd.concat([DF3,DF4])
DF3 = DF3.drop_duplicates(subset='Identificacion', keep='first')
Cruce
DF = DF3.merge(DF1_BC, how = 'inner', on = ['Identificacion'])
DF['IdColaborador'] = np.where((DF.DiasVencido > 60) & (DF.IdColaborador == "Colaborador"),"Ex Colaborador" ,DF.IdColaborador)
DF
| Identificacion | IdColaborador | Nombre | DiasVencido | FechaProceso | Operacion | YearEmision | SaldoSb204 | SaldoDificul | SaldoDificul30 | SaldoDificul60 | SegmentoFinal | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 1705894713 | Ex Colaborador | Carrera Torres Maria Soledad | 0.0 | 2022-09-30 | 314934 | 2010 | 18168.19 | 0.0 | 0.0 | 0.0 | Vivienda |
| 1 | 1705894713 | Ex Colaborador | Carrera Torres Maria Soledad | 0.0 | 2022-10-31 | 314934 | 2010 | 17711.27 | 0.0 | 0.0 | 0.0 | Vivienda |
| 2 | 1705894713 | Ex Colaborador | Carrera Torres Maria Soledad | 0.0 | 2022-11-30 | 314934 | 2010 | 17255.35 | 0.0 | 0.0 | 0.0 | Vivienda |
| 3 | 1705894713 | Ex Colaborador | Carrera Torres Maria Soledad | 0.0 | 2022-12-30 | 314934 | 2010 | 16791.59 | 0.0 | 0.0 | 0.0 | Vivienda |
| 4 | 1705894713 | Ex Colaborador | Carrera Torres Maria Soledad | 0.0 | 2022-03-31 | 314934 | 2010 | 20820.07 | 0.0 | 0.0 | 0.0 | Vivienda |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 10628 | 0914819826 | Ex Colaborador | Rodriguez Dominguez Eduardo Luis | 0.0 | 2021-06-30 | 100307729 | 2007 | 10508.52 | 0.0 | 0.0 | 0.0 | Vivienda |
| 10629 | 0914819826 | Ex Colaborador | Rodriguez Dominguez Eduardo Luis | 8.0 | 2021-07-30 | 100307729 | 2007 | 10508.52 | 0.0 | 0.0 | 0.0 | Vivienda |
| 10630 | 0914819826 | Ex Colaborador | Rodriguez Dominguez Eduardo Luis | 9.0 | 2021-08-31 | 100307729 | 2007 | 9798.69 | 0.0 | 0.0 | 0.0 | Vivienda |
| 10631 | 0914819826 | Ex Colaborador | Rodriguez Dominguez Eduardo Luis | 0.0 | 2021-09-30 | 100307729 | 2007 | 8869.69 | 0.0 | 0.0 | 0.0 | Vivienda |
| 10632 | 0914819826 | Ex Colaborador | Rodriguez Dominguez Eduardo Lu | 7.0 | 2021-10-29 | 100307729 | 2007 | 8869.69 | 0.0 | 0.0 | 0.0 | Vivienda |
10633 rows × 12 columns
Consumo
DF1 = DF[DF.SegmentoFinal == 'Consumo']
DF1 = (DF1
.groupby(['FechaProceso','IdColaborador'], as_index=False)
.agg(Dificultades60 = ('SaldoDificul30', np.nansum), Dificultades30 = ('SaldoDificul30', np.nansum),SaldoTotal = ('SaldoSb204', np.nansum))
.reset_index()
)
DF1 = DF1.assign( Morosidad30 = DF1.Dificultades30/DF1.SaldoTotal, Morosidad60 = DF1.Dificultades60/DF1.SaldoTotal)
DF1['Morosidad60_pct'] = DF1['Morosidad60'] * 100
DF1['Morosidad30_pct'] = DF1['Morosidad30'] * 100
fig = go.Figure()
colores = {
'Colaborador': '#1f77b4',
'Ex Colaborador': '#d62728'
}
tipos = DF1['IdColaborador'].unique()
for tipo in tipos:
df_tipo = DF1[DF1['IdColaborador'] == tipo]
# === BARRA apilada (saldo) === con leyenda activa
fig.add_trace(go.Bar(
x=df_tipo['FechaProceso'],
y=df_tipo['SaldoTotal'],
name=tipo, # Solo esta traza tiene showlegend=True
marker_color=colores[tipo],
opacity=0.5,
yaxis='y2',
legendgroup=tipo,
showlegend=True
))
# === LÍNEA de morosidad === sin leyenda (agrupada visualmente)
fig.add_trace(go.Scatter(
x=df_tipo['FechaProceso'],
y=df_tipo['Morosidad30'] * 100,
mode='lines+markers',
name=tipo, # Mismo nombre pero sin leyenda
marker_color=colores[tipo],
legendgroup=tipo,
showlegend=False,
hovertemplate='<b>Fecha:</b> %{x|%Y-%m-%d}<br><b>Morosidad 60:</b> %{y:.1f}%<extra></extra>'
))
fig.update_layout(
title='Consumo - Morosidad 30',
xaxis_title='Fecha de Proceso',
yaxis=dict(title='Morosidad 30 (%)', tickformat='.1f'),
yaxis2=dict(title='Saldo Total', overlaying='y', side='right', showgrid=False),
barmode='stack',
plot_bgcolor='white',
paper_bgcolor='white',
legend=dict(orientation='h', y=1.1, x=0.5, xanchor='center')
)
fig.show()
Vivienda
DF1 = DF[DF.SegmentoFinal == 'Vivienda']
DF1 = (DF1
.groupby(['FechaProceso','IdColaborador'], as_index=False)
.agg(Dificultades60 = ('SaldoDificul30', np.nansum), Dificultades30 = ('SaldoDificul30', np.nansum),SaldoTotal = ('SaldoSb204', np.nansum))
.reset_index()
)
DF1 = DF1.assign( Morosidad30 = DF1.Dificultades30/DF1.SaldoTotal, Morosidad60 = DF1.Dificultades60/DF1.SaldoTotal)
DF1['Morosidad60_pct'] = DF1['Morosidad60'] * 100
DF1['Morosidad30_pct'] = DF1['Morosidad30'] * 100
fig = go.Figure()
colores = {
'Colaborador': '#1f77b4',
'Ex Colaborador': '#d62728'
}
tipos = DF1['IdColaborador'].unique()
for tipo in tipos:
df_tipo = DF1[DF1['IdColaborador'] == tipo]
# === BARRA apilada (saldo) === con leyenda activa
fig.add_trace(go.Bar(
x=df_tipo['FechaProceso'],
y=df_tipo['SaldoTotal'],
name=tipo, # Solo esta traza tiene showlegend=True
marker_color=colores[tipo],
opacity=0.5,
yaxis='y2',
legendgroup=tipo,
showlegend=True
))
# === LÍNEA de morosidad === sin leyenda (agrupada visualmente)
fig.add_trace(go.Scatter(
x=df_tipo['FechaProceso'],
y=df_tipo['Morosidad60'] * 100,
mode='lines+markers',
name=tipo, # Mismo nombre pero sin leyenda
marker_color=colores[tipo],
legendgroup=tipo,
showlegend=False,
hovertemplate='<b>Fecha:</b> %{x|%Y-%m-%d}<br><b>Morosidad 60:</b> %{y:.1f}%<extra></extra>'
))
fig.update_layout(
title='Vivienda - Morosidad 60',
xaxis_title='Fecha de Proceso',
yaxis=dict(title='Morosidad 60 (%)', tickformat='.1f'),
yaxis2=dict(title='Saldo Total', overlaying='y', side='right', showgrid=False),
barmode='stack',
plot_bgcolor='white',
paper_bgcolor='white',
legend=dict(orientation='h', y=1.1, x=0.5, xanchor='center')
)
fig.show()